﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_FIM_MoneyRequestDtlsRates : IDAL_FIM_MoneyRequestDtlsRates
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public IList<M_FIM_MoneyRequestDtlsRates> GetFull(Nullable<int> money_req_dtls_seq , Nullable<int> exp_rate_seq)
        {
            //int n = exp_rate_type_seq.Length;
            //string strJoin = "";
            //string strFilter = "";

            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyRequestDtlsRates> infoList = new List<M_FIM_MoneyRequestDtlsRates>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_dtls_seq", money_req_dtls_seq);
                dbManager.AddParameters(2, "@exp_rate_seq", exp_rate_seq);

                //for (int i = 0; i < n; i++)
                //{
                //    dbManager.AddParameters(3 + i, "@exp_rate_type_seq" + i.ToString(), exp_rate_type_seq[i]);
                //    strFilter += (exp_rate_type_seq[i] != null ?
                //        string.Format(" and map{0}.exp_rate_type_seq = @exp_rate_type_seq{0}", i.ToString()) : "");
                //    //strSelect += string.Format(", map{0}.exp_rate_type_seq", i.ToString());
                //    strJoin += string.Format(" LEFT JOIN fim_mas_expense_rate_mappings map{0} ON rate.exp_rate_seq = map{0}.exp_rate_seq", i.ToString());
                //}

                strSQL = "SELECT rdrate.money_req_dtls_rate_seq"
                    + ", rd.money_req_dtls_seq, rate.exp_rate_seq"
                    + ", coalesce(rdrate.amount, 0) as amount"
                    + ", coalesce(rdrate.man, 1) as man"
                    + ", coalesce(rdrate.day, 1) as day"
                    + ", coalesce(rdrate.hour, 1) as hour"
                    + ", coalesce(rdrate.minute, 1) as minute"
                    + ", rdrate.emp_seq, rdrate.class_seq"

                    + ", rate.relate_man, rate.relate_day"

                    + ", rate.exp_rate_name, rate.exp_rate_descr, pjexp.bgm_exp_seq, rate.amount_min, rate.amount_max"
                    + ", coalesce(rdrate.amount, 0) * coalesce(rdrate.man, 1) * coalesce(rdrate.day, 1)"
                        + " * coalesce(rdrate.hour, 1) * coalesce(rdrate.minute, 1) as total_amount"
                    + " FROM fim_money_request_dtls rd"
                    + " LEFT JOIN bgm_project_operation_expense opexp ON rd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense pjexp ON opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                    + " LEFT JOIN fim_mas_expense_rates rate ON pjexp.bgm_source_seq = rate.bgm_source_seq"
                        + " AND pjexp.bgm_exp_seq = rate.bgm_exp_seq"
                    //+ strJoin
                    + " LEFT JOIN fim_money_request_dtls_rates rdrate ON rd.money_req_dtls_seq = rdrate.money_req_dtls_seq "
                        + " AND rate.exp_rate_seq = rdrate.exp_rate_seq"

                    + " WHERE 1=1 /* rdexp.record_status not like @delete_status*/"
                    + (money_req_dtls_seq != null ? " AND rd.money_req_dtls_seq = @money_req_dtls_seq" : "")
                    + (exp_rate_seq != null ? " AND rate.exp_rate_seq = @exp_rate_seq" : "");
                    //+ strFilter;

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyRequestDtlsRates info = new M_FIM_MoneyRequestDtlsRates();
                        info.money_req_dtls_rate_seq = cn.ConvertNullInt(drc[i]["money_req_dtls_rate_seq"]);
                        info.money_req_dtls_seq = cn.ConvertNullInt(drc[i]["money_req_dtls_seq"]);
                        info.exp_rate_seq = cn.ConvertNullInt(drc[i]["exp_rate_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.man = cn.ConvertNullInt(drc[i]["man"]);
                        info.day = cn.ConvertNullDouble(drc[i]["day"]);
                        info.hour = cn.ConvertNullDouble(drc[i]["hour"]);
                        info.minute = cn.ConvertNullDouble(drc[i]["minute"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.class_seq = cn.ConvertNullInt(drc[i]["class_seq"]);

                        info.relate_man = drc[i]["relate_man"].ToString();
                        info.relate_day = drc[i]["relate_day"].ToString();

                        info.exp_rate_name = drc[i]["exp_rate_name"].ToString();
                        info.exp_rate_descr = drc[i]["exp_rate_descr"].ToString();
                        info.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);
                        info.amount_min = cn.ConvertNullDouble(drc[i]["amount_min"]);
                        info.amount_max = cn.ConvertNullDouble(drc[i]["amount_max"]);
                        info.total_amount = cn.ConvertNullDouble(drc[i]["total_amount"]);

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MoneyRequestDtlsRates> GetFull2(Nullable<int> bgm_proj_oper_exp_seq, Nullable<int> exp_rate_seq)
        {
            //int n = exp_rate_type_seq.Length;
            //string strJoin = "";
            //string strFilter = "";

            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyRequestDtlsRates> infoList = new List<M_FIM_MoneyRequestDtlsRates>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_exp_seq", bgm_proj_oper_exp_seq);
                dbManager.AddParameters(2, "@exp_rate_seq", exp_rate_seq);

                //for (int i = 0; i < n; i++)
                //{
                //    dbManager.AddParameters(3 + i, "@exp_rate_type_seq" + i.ToString(), exp_rate_type_seq[i]);
                //    strFilter += (exp_rate_type_seq[i] != null ?
                //        string.Format(" and map{0}.exp_rate_type_seq = @exp_rate_type_seq{0}", i.ToString()) : "");
                //    //strSelect += string.Format(", map{0}.exp_rate_type_seq", i.ToString());
                //    strJoin += string.Format(" LEFT JOIN fim_mas_expense_rate_mappings map{0} ON rate.exp_rate_seq = map{0}.exp_rate_seq", i.ToString());
                //}

                strSQL = "SELECT null as money_req_dtls_rate_seq"
                    + ", null as money_req_dtls_seq, rate.exp_rate_seq"
                    + ", 0 as amount"
                    + ", 1 as man"
                    + ", 1 as day"
                    + ", 1 as hour"
                    + ", 1 as minute"
                    + ", null as emp_seq, null as class_seq"

                    + ", rate.relate_man, rate.relate_day"

                    + ", rate.exp_rate_name, rate.exp_rate_descr, pjexp.bgm_exp_seq, rate.amount_min, rate.amount_max"
                    + ", 0 as total_amount"
                    //+ " FROM fim_money_request_dtls rd"
                    //+ " LEFT JOIN bgm_project_operation_expense opexp ON rd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " FROM bgm_project_operation_expense opexp"
                    + " LEFT JOIN bgm_project_expense pjexp ON opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                    + " LEFT JOIN fim_mas_expense_rates rate ON pjexp.bgm_source_seq = rate.bgm_source_seq"
                        + " AND pjexp.bgm_exp_seq = rate.bgm_exp_seq"
                    //+ strJoin
                    //+ " LEFT JOIN bgm_project_operation_expense_rate oprate ON opexp.bgm_proj_oper_exp_seq = oprate.bgm_proj_oper_exp_seq"
                    //    + " AND rate.exp_rate_seq = oprate.exp_rate_seq"

                    + " WHERE 0 = 0"
                    + (bgm_proj_oper_exp_seq != null ? " AND opexp.bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq" : "")
                    + (exp_rate_seq != null ? " AND rate.exp_rate_seq = @exp_rate_seq" : "");
                    //+ strFilter;

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyRequestDtlsRates info = new M_FIM_MoneyRequestDtlsRates();
                        info.money_req_dtls_rate_seq = cn.ConvertNullInt(drc[i]["money_req_dtls_rate_seq"]);
                        info.money_req_dtls_seq = cn.ConvertNullInt(drc[i]["money_req_dtls_seq"]);
                        info.exp_rate_seq = cn.ConvertNullInt(drc[i]["exp_rate_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.man = cn.ConvertNullInt(drc[i]["man"]);
                        info.day = cn.ConvertNullDouble(drc[i]["day"]);
                        info.hour = cn.ConvertNullDouble(drc[i]["hour"]);
                        info.minute = cn.ConvertNullDouble(drc[i]["minute"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.class_seq = cn.ConvertNullInt(drc[i]["class_seq"]);

                        info.relate_man = drc[i]["relate_man"].ToString();
                        info.relate_day = drc[i]["relate_day"].ToString();

                        info.exp_rate_name = drc[i]["exp_rate_name"].ToString();
                        info.exp_rate_descr = drc[i]["exp_rate_descr"].ToString();
                        info.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);
                        info.amount_min = cn.ConvertNullDouble(drc[i]["amount_min"]);
                        info.amount_max = cn.ConvertNullDouble(drc[i]["amount_max"]);
                        info.total_amount = cn.ConvertNullDouble(drc[i]["total_amount"]);

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_MoneyRequestDtlsRates GetInfo(int money_req_dtls_seq, int emp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MoneyRequestDtlsRates info = new M_FIM_MoneyRequestDtlsRates();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@money_req_dtls_seq", money_req_dtls_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);

                strSQL = "select money_req_dtls_rate_seq"
                    + ", money_req_dtls_seq, exp_rate_seq, amount"
                    + ", man, day, hour, minute, emp_seq, class_seq"

                    + ", coalesce(amount, 1) * coalesce(man, 1) * coalesce(day, 1)"
                        + " * coalesce(hour, 1) * coalesce(minute, 1) as total_amount"
                    + " from fim_money_request_dtls_rates"
                    + " where money_req_dtls_seq = @money_req_dtls_seq"
                    + " and emp_seq = @emp_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.money_req_dtls_rate_seq = cn.ConvertNullInt(drc[0]["money_req_dtls_rate_seq"]);
                    info.money_req_dtls_seq = cn.ConvertNullInt(drc[0]["money_req_dtls_seq"]);
                    info.exp_rate_seq = cn.ConvertNullInt(drc[0]["exp_rate_seq"]);
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.man = cn.ConvertNullInt(drc[0]["man"]);
                    info.day = cn.ConvertNullDouble(drc[0]["day"]);
                    info.hour = cn.ConvertNullDouble(drc[0]["hour"]);
                    info.minute = cn.ConvertNullDouble(drc[0]["minute"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.class_seq = cn.ConvertNullInt(drc[0]["class_seq"]);
                    info.total_amount = cn.ConvertNullDouble(drc[0]["total_amount"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_FIM_MoneyRequestDtlsRates GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MoneyRequestDtlsRates info = new M_FIM_MoneyRequestDtlsRates();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT rdrate.money_req_dtls_rate_seq"
                    + ", rdrate.money_req_dtls_seq, rdrate.exp_rate_seq"
                    + ", rdrate.amount, rdrate.man, rdrate.day, rdrate.hour, rdrate.minute"
                    + ", rdrate.emp_seq, rdrate.class_seq"

                    + ", rate.exp_rate_name, pjexp.bgm_exp_seq, rate.amount_min, rate.amount_max"
                    + ", coalesce(rdrate.amount, 1) * coalesce(rdrate.man, 1) * coalesce(rdrate.day, 1)"
                        + " * coalesce(rdrate.hour, 1) * coalesce(rdrate.minute, 1) as total_amount"
                    + " FROM fim_money_request_dtls rd"
                    + " LEFT JOIN bgm_project_operation_expense opexp ON rd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense pjexp ON opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                    + " LEFT JOIN fim_mas_expense_rates rate ON opexp.bgm_source_seq = rate.bgm_source_seq"
                        + " AND pjexp.bgm_exp_seq = rate.bgm_exp_seq"
                    + " LEFT JOIN fim_money_request_dtls_rates rdrate ON rd.money_req_dtls_seq = rdrate.money_req_dtls_seq"
                        + " AND rate.exp_rate_seq = rdrate.exp_rate_seq"

                    + " WHERE rd.money_req_dtls_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.money_req_dtls_rate_seq = cn.ConvertNullInt(drc[0]["money_req_dtls_rate_seq"]);
                    info.money_req_dtls_seq = cn.ConvertNullInt(drc[0]["money_req_dtls_seq"]);
                    info.exp_rate_seq = cn.ConvertNullInt(drc[0]["exp_rate_seq"]);
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.man = cn.ConvertNullInt(drc[0]["man"]);
                    info.day = cn.ConvertNullDouble(drc[0]["day"]);
                    info.hour = cn.ConvertNullDouble(drc[0]["hour"]);
                    info.minute = cn.ConvertNullDouble(drc[0]["minute"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.class_seq = cn.ConvertNullInt(drc[0]["class_seq"]);

                    info.exp_rate_name = drc[0]["exp_rate_name"].ToString();
                    info.bgm_exp_seq = cn.ConvertNullInt(drc[0]["bgm_exp_seq"]);
                    info.amount_min = cn.ConvertNullDouble(drc[0]["amount_min"]);
                    info.amount_max = cn.ConvertNullDouble(drc[0]["amount_max"]);
                    info.total_amount = cn.ConvertNullDouble(drc[0]["total_amount"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public string Insert(M_FIM_MoneyRequestDtlsRates info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@money_req_dtls_rate_seq", info.money_req_dtls_rate_seq);
                dbManager.AddParameters(1, "@money_req_dtls_seq", info.money_req_dtls_seq);
                dbManager.AddParameters(2, "@exp_rate_seq", info.exp_rate_seq);
                dbManager.AddParameters(3, "@amount", info.amount);
                dbManager.AddParameters(4, "@man", info.man);
                dbManager.AddParameters(5, "@day", info.day);
                dbManager.AddParameters(6, "@hour", info.hour);
                dbManager.AddParameters(7, "@minute", info.minute);
                dbManager.AddParameters(8, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(9, "@class_seq", info.class_seq);

                dbManager.AddParameters(10, "@created_by", info.created_by);
                dbManager.AddParameters(11, "@created_when", info.created_when);

                strSQL = "INSERT INTO fim_money_request_dtls_rates"
                    + "(money_req_dtls_seq, exp_rate_seq, amount"
                    + ", man, day, hour, minute"
                    + ", emp_seq, class_seq"
                    + ", created_by, created_when)"
                    + " VALUES (@money_req_dtls_seq"
                        + ", @exp_rate_seq"
                        + ", @amount"
                        + ", @man"
                        + ", @day"
                        + ", @hour"
                        + ", @minute"
                        + ", @emp_seq"
                        + ", @class_seq"

                        + ", @created_by"
                        + ", @created_when"
                        + ");"
                    + " select scope_identity()";
                var strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                return strResult;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_MoneyRequestDtlsRates info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@money_req_dtls_rate_seq", info.money_req_dtls_rate_seq);
                dbManager.AddParameters(1, "@money_req_dtls_seq", info.money_req_dtls_seq);
                dbManager.AddParameters(2, "@exp_rate_seq", info.exp_rate_seq);
                dbManager.AddParameters(3, "@amount", info.amount);
                dbManager.AddParameters(4, "@man", info.man);
                dbManager.AddParameters(5, "@day", info.day);
                dbManager.AddParameters(6, "@hour", info.hour);
                dbManager.AddParameters(7, "@minute", info.minute);
                dbManager.AddParameters(8, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(9, "@class_seq", info.class_seq);

                dbManager.AddParameters(10, "@updated_by", info.updated_by);
                dbManager.AddParameters(11, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_money_request_dtls_rates"
                    + " SET money_req_dtls_seq = @money_req_dtls_seq"
                    + ", exp_rate_seq = @exp_rate_seq"
                    + ", amount = @amount"
                    + ", man = @man"
                    + ", day = @day"
                    + ", hour = @hour"
                    + ", minute = @minute"
                    + ", emp_seq = @emp_seq"
                    + ", class_seq = @class_seq"
                    
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE money_req_dtls_rate_seq = @money_req_dtls_rate_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_MoneyRequestDtlsRates info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@money_req_dtls_rate_seq", info.money_req_dtls_rate_seq);
                //dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(2, "@updated_by", info.updated_by);
                //dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "DELETE FROM fim_money_request_dtls_rates"
                    + " WHERE money_req_dtls_rate_seq = @money_req_dtls_rate_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}
    }
}
